package com.situ.personal.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.situ.personal.entity.User;
import com.situ.personal.entity.UserDept;
import com.situ.personal.util.JDBCUtil;
import com.situ.personal.util.ModelConvertUtil;

public class UserDao {

	public int getTotalCount() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int count = 0;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "select count(*) from user";
			preparedStatement = connection.prepareStatement(sql);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				count = resultSet.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, resultSet);
		}
		return count;
	}

	public List<User> findByPage1(int offset, int pageSize) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<User> list = new ArrayList<User>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "select id,name,age,password,email,department_id from user limit ?,?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, offset);
			preparedStatement.setInt(2, pageSize);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String password =  resultSet.getString("password");
				String email = resultSet.getString("email");
				int departmentId =  resultSet.getInt("department_id");
				User user = new User(id, name, age, password, email, departmentId);
				list.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}
	
	public List<UserDept> findByPage2(int offset, int pageSize) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<UserDept> list = new ArrayList<UserDept>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT u.id,u.name,u.age,u.email,u.password,d.name as deptName FROM `user` AS u LEFT JOIN department AS d ON u.department_id=d.id limit ?,?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, offset);
			preparedStatement.setInt(2, pageSize);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String password =  resultSet.getString("password");
				String email = resultSet.getString("email");
				String deptName = resultSet.getString("deptName");
				UserDept userDept = new UserDept(id, name, age, password, email, deptName);
				list.add(userDept);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}
	
	public List<Map<String, Object>> findByPage3(int offset, int pageSize) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		// List<UserDept> list = new ArrayList<UserDept>();
		List<Map<String, Object>> list = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT u.id,u.name,u.age,u.email,u.password,d.name as deptName FROM `user` AS u left JOIN department AS d ON u.department_id=d.id limit ?,?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, offset);
			preparedStatement.setInt(2, pageSize);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			list = ModelConvertUtil.convertToList(resultSet);
//			while (resultSet.next()) {
//				int id = resultSet.getInt("id");
//				String name = resultSet.getString("name");
//				int age = resultSet.getInt("age");
//				String password =  resultSet.getString("password");
//				String email = resultSet.getString("email");
//				String deptName = resultSet.getString("deptName");
//			       这些查询的结果是不对应一张真实的表，是查询的结果集，但是可以把他当成一张表来使用，
//  			这样写的缺点就是：每次多表查询的结果都需要封装一个新的类来描述他。
//				// UserDept userDept = new UserDept(id, name, age, password, email, deptName);
//				// list.add(userDept);
//				// 每遍历一次就是封装查询的一行数据
//				// id   	--   1
//				// name     --   zhangsan
//				// age      --   23
//				// deptName --   软件部
//				// Map是一个集合，可以放多个key-value数据
//				Map<String, Object> map = new HashMap<String, Object>();
//				map.put("id", id);
//				map.put("name", name);
//				map.put("age", age);
//				map.put("deptName", deptName);
//				list.add(map);
//			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}

	public int insert(User user) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		int count = 0;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "INSERT INTO user(`name`,age,email,password,department_id) VALUES(?,?,?,?,?)";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, user.getName());
			preparedStatement.setInt(2, user.getAge());
			preparedStatement.setString(3, user.getEmail());
			preparedStatement.setString(4, user.getPassword());
			preparedStatement.setInt(5, user.getDepartmentId());
			count = preparedStatement.executeUpdate();
			if (count == 1) {
				System.out.println("插入成功");
			} else {
				System.out.println("插入失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
		
		return count;
	}

	public int deleteById(int id) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		int count = 0;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "DELETE FROM user WHERE id=?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			System.out.println(preparedStatement);
			count = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	public List<User> findAll() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<User> list = new ArrayList<User>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "select id,name,age,password,email,department_id from user";
			preparedStatement = connection.prepareStatement(sql);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String password =  resultSet.getString("password");
				String email = resultSet.getString("email");
				int departmentId =  resultSet.getInt("department_id");
				User user = new User(id, name, age, password, email, departmentId);
				list.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}
	
	public User login(String name, String password) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		User user = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "select id,name,age,email,password,department_id from user where name=? and password=?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, name);
			preparedStatement.setString(2, password);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String userName = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String email = resultSet.getString("email");
				String userPassword = resultSet.getString("password");
				int departmentId = resultSet.getInt("department_id");
				user = new User(id, userName, age, userPassword, email, departmentId);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return user;
	}

}
